﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace csharpbasics
{
    class MainEntryPoint
    {
        public static void Main()
        {
            Console.WriteLine("*************DatabaseTest*************");
            test1();
            testDataSet();
            testLinq();
            testLinqToSql();
        }

        public static void testLinqToSql()
        {
            string source = @"server=FHWS71403\sqlexpress;" +
                           "integrated security=SSPI;" +
                //"User Id=siva;Password=pwd;"+
                           "database=demo-db";
            DataContext db = new DataContext(source);
            Table<User> users = db.GetTable<User>();

            var userDetails = from c in users
                              orderby c.name
                              select c;
            foreach (var user in userDetails)
            {
                Console.WriteLine(user.email);
            }
        }

        public static void testLinq()
        {
            string[] names = { "Life is Beautiful",
                                "Arshika Agarwal",
                                "Seven Pounds",
                                "Rupali Agarwal",
                                "Pearl Solutions",
                                "Vamika Agarwal",
                                "Vidya Vrat Agarwal",
                                "Lionbridge Technologies"
                                };
            //Linq query
            IEnumerable<string> namesOfPeople = from name in names
                                                where name.Length <= 16
                                                select name;
            foreach (var name in namesOfPeople)
            {
                Console.WriteLine(name + "\n");
            }
        }
        public static void testDataSet()
        {
            string source = @"server=FHWS71403\sqlexpress;" +
                           "integrated security=SSPI;" +
                //"User Id=siva;Password=pwd;"+
                           "database=demo-db";
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(source);
                conn.Open();

                //conn.ChangeDatabase("AdventureWorks");

                string query = "select * from users";
                SqlCommand cmd = new SqlCommand(query, conn);
                /*
                    SqlDataAdapter da = new SqlDataAdapter();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                    SqlDataAdapter da = new SqlDataAdapter(sql, connString);
                 */
                //SqlDataReader reader = cmd.ExecuteReader();


                SqlDataAdapter da = new SqlDataAdapter(cmd);
                SqlCommandBuilder cb = new SqlCommandBuilder(da);
                DataSet ds = new DataSet();
                da.Fill(ds, "users");
                //ds.WriteXml(@"d:\productstable.xml");

                DataTable usersTbl = ds.Tables["users"];

                usersTbl.Rows[0]["email"] = "siva@yahoo.com";

                DataRow newRow = usersTbl.NewRow();
                newRow["name"] = "ding1";
                newRow["email"] = "dong1";
                newRow["phone"] = "33333333331";
                newRow["dob"] = DateTime.Now;
                usersTbl.Rows.Add(newRow);
                /*
                SqlCommand insCmd = new SqlCommand(@"insert into users(name,email,phone,dob) values(@name,@email,@phone,@dob)", conn);
                insCmd.Parameters.Add("@name", SqlDbType.NVarChar, 30, "name");
                insCmd.Parameters.Add("@email", SqlDbType.NVarChar, 30, "email");
                insCmd.Parameters.Add("@phone", SqlDbType.NVarChar, 30, "phone");
                insCmd.Parameters.Add("@dob", SqlDbType.Date, 30, "dob");

                da.InsertCommand = insCmd;
                */
                da.Update(ds, "users");


                foreach (DataRow row in usersTbl.Rows)
                {
                    Console.WriteLine(row["id"] + ":" + row["email"]);
                }


                //reader.Close();
            }
            catch (SqlException e)
            {
                Console.WriteLine("Error: " + e.StackTrace.ToString());
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        public static void test1()
        {
            string source = @"server=FHWS71403\sqlexpress;" +
                           "integrated security=SSPI;" +
                //"User Id=siva;Password=pwd;"+
                           "database=demo-db";
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(source);
                conn.Open();

                //conn.ChangeDatabase("AdventureWorks");

                string query = "select * from users";
                //SqlCommand cmd = new SqlCommand(query, conn);
                SqlCommand cmd1 = new SqlCommand();
                cmd1.CommandText = query;
                cmd1.Connection = conn;
                SqlDataReader reader = cmd1.ExecuteReader();

                //reader.GetDataTypeName(1);
                //reader.GetName(1);
                //reader.GetOrdinal("id");
                //reader.GetFieldType(1);

                /*
                DataTable schema = reader.GetSchemaTable();

                foreach (DataRow row in schema.Rows)
                {
                    foreach (DataColumn col in schema.Columns)
                    {
                        Console.WriteLine(col.ColumnName + " = " + row[col]);                        
                    }
                    
                }
                */

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Console.WriteLine(reader[0] + ":" + reader[1]);
                        Console.WriteLine(reader["id"] + ":" + reader["name"]);
                        Console.WriteLine(reader.GetInt32(0).ToString() + ":" + reader.GetString(1));


                    }
                }

                reader.Close();
            }
            catch (SqlException e)
            {
                Console.WriteLine("Error: " + e.StackTrace.ToString());
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
    }
}
